﻿using ChurchDiary.Common;
using ChurchDiary.DAL.Contract;
using ChurchDiary.DAL.DAL;
using ChurchDiary.TableEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ChurchDiary.DAL.Methods
{
    public class DAL_Administration : DataAccess, IAdministration
    {
        #region Pages
        public List<EntityPage> GetPages(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityPage> Pages = new List<EntityPage>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Pages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Pages;
                            }
                            while (OReader.Read())
                            {
                                EntityPage Page = new EntityPage();
                                Page.PageId = (int)OReader["PageId"];
                                Page.PageName = OReader["PageName"].ToString();
                                Page.PageTitle = OReader["PageTitle"].ToString();
                                Page.CurrentStatus = (int)OReader["CurrentStatus"];
                                Page.PageDescription = OReader["PageDescription"].ToString();
                                Page.TotalRecords = (int)OReader["TotalRecords"];
                                Page.ActionName = OReader["ActionName"].ToString();
                                Page.ControllerName = OReader["ControllerName"].ToString();
                                Pages.Add(Page);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Pages;
        }
        public List<EntityPage> GetPages()
        {
            List<EntityPage> Pages = new List<EntityPage>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Pages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Pages;
                            }
                            while (OReader.Read())
                            {
                                EntityPage Page = new EntityPage();
                                Page.PageId = (int)OReader["PageId"];
                                Page.PageName = OReader["PageName"].ToString();
                                Page.PageTitle = OReader["PageTitle"].ToString();
                                Page.CurrentStatus = (int)OReader["CurrentStatus"];
                                Page.PageDescription = OReader["PageDescription"].ToString();
                                Page.TotalRecords = (int)OReader["TotalRecords"];
                                Page.ActionName = OReader["ActionName"].ToString();
                                Page.ControllerName = OReader["ControllerName"].ToString();
                                Pages.Add(Page);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Pages;
        }
        public string InsertUpdateNewPages(EntityPage Page)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Pages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ControllerName", DbType.String, Page.ControllerName);
                    SQLServer.AddInParameter(dbCommand, "@ActionName", DbType.String, Page.ActionName);
                    SQLServer.AddInParameter(dbCommand, "@PageTitle", DbType.String, Page.PageTitle);
                    SQLServer.AddInParameter(dbCommand, "@PageName", DbType.String, Page.PageName);
                    SQLServer.AddInParameter(dbCommand, "@PageDesc", DbType.String, Page.PageDescription);
                    SQLServer.AddInParameter(dbCommand, "@PageId", DbType.Int16, Page.PageId);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Page.CreatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, Page.CreatedOn);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public EntityPage GetPage(int PageId)
        {
            EntityPage Page = new EntityPage();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Pages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@PageId", DbType.Int32, PageId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Page;
                            }
                            while (OReader.Read())
                            {
                                Page.PageId = (int)OReader["PageId"];
                                Page.PageName = OReader["PageName"].ToString();
                                Page.PageTitle = OReader["PageTitle"].ToString();
                                Page.CurrentStatus = (int)OReader["CurrentStatus"];
                                Page.PageDescription = OReader["PageDescription"].ToString();
                                Page.TotalRecords = (int)OReader["TotalRecords"];
                                Page.ActionName = OReader["ActionName"].ToString();
                                Page.ControllerName = OReader["ControllerName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Page;
        }
        public string UpdatePage(EntityPage Page)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Pages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ControllerName", DbType.String, Page.ControllerName);
                    SQLServer.AddInParameter(dbCommand, "@ActionName", DbType.String, Page.ActionName);
                    SQLServer.AddInParameter(dbCommand, "@PageTitle", DbType.String, Page.PageTitle);
                    SQLServer.AddInParameter(dbCommand, "@PageName", DbType.String, Page.PageName);
                    SQLServer.AddInParameter(dbCommand, "@PageDesc", DbType.String, Page.PageDescription);
                    SQLServer.AddInParameter(dbCommand, "@PageId", DbType.Int16, Page.PageId);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Page.CreatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, Page.CreatedOn);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public EntityPage GetPage(string ControllerName, string ActionName)
        {
            EntityPage Page = new EntityPage();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PagePageSection]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "PAGE");
                    SQLServer.AddInParameter(dbCommand, "@ControllerName", DbType.String, ControllerName);
                    SQLServer.AddInParameter(dbCommand, "@ActionName", DbType.String, ActionName);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, Current.LanaguageId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Page;
                            }
                            while (OReader.Read())
                            {
                                Page.PageId = (int)OReader["PageId"];
                                Page.PageName = OReader["PageName"].ToString();
                                Page.PageTitle = OReader["PageTitle"].ToString();
                                Page.CurrentStatus = (int)OReader["CurrentStatus"];
                                Page.PageDescription = OReader["PageDescription"].ToString();
                                Page.TotalRecords = (int)OReader["TotalRecords"];
                                Page.ActionName = OReader["ActionName"].ToString();
                                Page.ControllerName = OReader["ControllerName"].ToString();
                            }
                            OReader.NextResult();
                            Page.PageSection = new List<EntityPageSection>();
                            while (OReader.Read())
                            {
                                EntityPageSection PageSection = new EntityPageSection();
                                PageSection.PageSection = (Guid)OReader["PageSection"];
                                PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                PageSection.LanguageId = (Guid)OReader["LanguageId"];
                                PageSection.TemplateContent = OReader["TemplateContent"].ToString();
                                PageSection.TemplateUrl = OReader["TemplateUrl"].ToString();
                                Page.PageSection.Add(PageSection);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Page;
        }
        #endregion
        #region PageSection Pk
        public string InsertPageSectionPk(EntityPageSectionPk PageSection)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSectionPk]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionName", DbType.String, PageSection.PageSectionName);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, PageSection.CreatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, PageSection.CreatedOn);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdatePageSectionPk(EntityPageSectionPk PageSection)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSectionPk]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionId", DbType.Guid, PageSection.PageSectionId);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionName", DbType.String, PageSection.PageSectionName);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, PageSection.UpdatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, PageSection.UpdatedOn);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeletePageSectionPk(Guid PageSectionId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSectionPk]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionId", DbType.Guid, PageSectionId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityPageSectionPk> GetPageSectionPk(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityPageSectionPk> PageSections = new List<EntityPageSectionPk>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PageSectionPk]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return PageSections;
                            }
                            while (OReader.Read())
                            {
                                EntityPageSectionPk PageSection = new EntityPageSectionPk();
                                PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                PageSection.PageSectionName = OReader["PageSectionName"].ToString();
                                PageSections.Add(PageSection);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return PageSections;
        }
        public List<EntityPageSectionPk> GetPageSectionPk()
        {
            List<EntityPageSectionPk> PageSections = new List<EntityPageSectionPk>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PageSectionPk]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return PageSections;
                            }
                            while (OReader.Read())
                            {
                                EntityPageSectionPk PageSection = new EntityPageSectionPk();
                                PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                PageSection.PageSectionName = OReader["PageSectionName"].ToString();
                                PageSections.Add(PageSection);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return PageSections;
        }
        public EntityPageSectionPk GetPageSectionPk(Guid PageSectionId)
        {
            EntityPageSectionPk PageSection = new EntityPageSectionPk();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PageSectionPk]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionId", DbType.Guid, PageSectionId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return PageSection;
                            }
                            while (OReader.Read())
                            {
                                PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                PageSection.PageSectionName = OReader["PageSectionName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return PageSection;
        }
        #endregion
        #region PageSection
        public string InsertPageSection(EntityPageSection PageSection)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSection]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@PageSection", DbType.Guid, PageSection.PageSection);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionId", DbType.Guid, PageSection.PageSectionId);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, PageSection.LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@TemplateContent", DbType.String, PageSection.TemplateContent);
                    SQLServer.AddInParameter(dbCommand, "@TemplateUrl", DbType.String, PageSection.TemplateUrl);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, PageSection.CreatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, PageSection.CreatedOn);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdatePageSection(EntityPageSection PageSection)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSection]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@PageSection", DbType.Guid, PageSection.PageSection);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionId", DbType.Guid, PageSection.PageSectionId);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, PageSection.LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@TemplateContent", DbType.String, PageSection.TemplateContent);
                    SQLServer.AddInParameter(dbCommand, "@TemplateUrl", DbType.String, PageSection.TemplateUrl);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, PageSection.CreatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, PageSection.CreatedOn);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeletePageSection(Guid PageSectionId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSection]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@PageSection", DbType.Guid, PageSectionId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityPageSection> GetPageSection(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage,Guid PageSectionId)
        {
            List<EntityPageSection> PageSections = new List<EntityPageSection>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PageSection]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    SQLServer.AddInParameter(dbCommand, "@PageSection", DbType.Guid, PageSectionId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return PageSections;
                            }
                            while (OReader.Read())
                            {
                                EntityPageSection PageSection = new EntityPageSection();
                                PageSection.PageSection = (Guid)OReader["PageSection"];
                                PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                PageSection.LanguageId = (Guid)OReader["LanguageId"];
                                PageSection.TemplateContent = OReader["TemplateContent"].ToString();
                                PageSection.TemplateUrl = OReader["TemplateUrl"].ToString();
                                PageSection.Language = new EntityLanguage();
                                PageSection.Language.LanguageName = OReader["LanguageName"].ToString();
                                PageSection.Language.LagnuageAbbrivation = OReader["LagnuageAbbrivation"].ToString();
                                PageSection.Language.LanguageValue = (Int32)OReader["LanguageValue"];
                                PageSection.Language.LanguageId = (Guid)OReader["LanguageId"];
                                PageSection.PageSectionPk = new EntityPageSectionPk();
                                PageSection.PageSectionPk.PageSectionName = OReader["PageSectionName"].ToString();
                                PageSections.Add(PageSection);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return PageSections;
        }
        public EntityPageSection GetPageSection(Guid PageSectionId)
        {
            EntityPageSection PageSection = new EntityPageSection();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PageSection]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@PageSection", DbType.Guid, PageSectionId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return PageSection;
                            }
                            while (OReader.Read())
                            {
                                PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                PageSection.PageSection = (Guid)OReader["PageSection"];
                                PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                PageSection.LanguageId = (Guid)OReader["LanguageId"];
                                PageSection.TemplateContent = OReader["TemplateContent"].ToString();
                                PageSection.TemplateUrl = OReader["TemplateUrl"].ToString();
                                PageSection.Language = new EntityLanguage();
                                PageSection.Language.LanguageName = OReader["LanguageName"].ToString();
                                PageSection.Language.LagnuageAbbrivation = OReader["LagnuageAbbrivation"].ToString();
                                PageSection.Language.LanguageValue = (Int32)OReader["LanguageValue"];
                                PageSection.Language.LanguageId = (Guid)OReader["LanguageId"];
                                PageSection.PageSectionPk = new EntityPageSectionPk();
                                PageSection.PageSectionPk.PageSectionName = OReader["PageSectionName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return PageSection;
        }
        #endregion
        #region PageSection Assignment
        public string InsertPageSectionAssignment(EntityPageSectionAssignment PageSectionAssignment)
        {
            string IResult = "SME";
            try
            {
                int Ordinal = 0;
                foreach (EntityPageSectionPk pageSection in PageSectionAssignment.PageSections)
                {
                    using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSectionAssignment]"))
                    {
                        SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                        SQLServer.AddInParameter(dbCommand, "@PageId", DbType.Int32, PageSectionAssignment.PageId);
                        SQLServer.AddInParameter(dbCommand, "@PageSectionId", DbType.Guid, pageSection.PageSectionId);
                        SQLServer.AddInParameter(dbCommand, "@Ordinal", DbType.Int32, Ordinal + 1);
                        SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                        SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                        using (DbConnection con = SQLServer.CreateConnection())
                        {
                            IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdatePageSectionAssignment(EntityPageSectionAssignment PageSectionAssignment)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSectionAssignment]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionAssignmentId", DbType.Guid, PageSectionAssignment.PageSectionAssignmentId);
                    SQLServer.AddInParameter(dbCommand, "@PageId", DbType.Int32, PageSectionAssignment.PageId);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionId", DbType.Guid, PageSectionAssignment.PageSectionId);
                    SQLServer.AddInParameter(dbCommand, "@Ordinal", DbType.Int32, PageSectionAssignment.Ordinal);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeletePageSectionAssignment(Guid PageSectionAssignmentId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PageSectionAssignment]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@PageSectionAssignmentId", DbType.Guid, PageSectionAssignmentId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public EntityPageSectionAssignment GetPageSectionAssignment(Guid EntityPageSectionAssignmentId)
        {
            EntityPageSectionAssignment EntityPageSectionAssignment = new EntityPageSectionAssignment();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PageSectionAssignment]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@AssignmentId", DbType.Guid, EntityPageSectionAssignmentId);
                    {
                        using (DbConnection con = SQLServer.CreateConnection())
                        {
                            using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                            {
                                if (OReader == null)
                                {
                                    return EntityPageSectionAssignment;
                                }
                                while (OReader.Read())
                                {
                                    EntityPageSectionAssignment.PageSectionAssignmentId = (Guid)OReader["PageSectionAssignmentId"]; ;
                                    EntityPageSectionAssignment.PageId = (Int32)OReader["PageId"];
                                    EntityPageSectionAssignment.PageSectionId = (Guid)OReader["PageSectionId"];
                                    EntityPageSectionAssignment.Ordinal = (Int32)OReader["Ordinal"];

                                    EntityPageSectionAssignment.Page = new EntityPage();
                                    EntityPageSectionAssignment.Page.PageId = (int)OReader["PageId"];
                                    EntityPageSectionAssignment.Page.PageName = OReader["PageName"].ToString();
                                    EntityPageSectionAssignment.Page.PageTitle = OReader["PageTitle"].ToString();
                                    EntityPageSectionAssignment.Page.CurrentStatus = (int)OReader["CurrentStatus"];
                                    EntityPageSectionAssignment.Page.PageDescription = OReader["PageDescription"].ToString();
                                    EntityPageSectionAssignment.Page.ActionName = OReader["ActionName"].ToString();
                                    EntityPageSectionAssignment.Page.ControllerName = OReader["ControllerName"].ToString();

                                    EntityPageSectionAssignment.PageSection = new EntityPageSectionPk();
                                    EntityPageSectionAssignment.PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                    EntityPageSectionAssignment.PageSection.PageSectionName = OReader["PageSectionName"].ToString();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return EntityPageSectionAssignment;
        }
        public List<EntityPageSectionAssignment> GetPageSectionAssignment(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityPageSectionAssignment> EntityPageSectionAssignments = new List<EntityPageSectionAssignment>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PageSectionAssignment]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    {
                        using (DbConnection con = SQLServer.CreateConnection())
                        {
                            using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                            {
                                if (OReader == null)
                                {
                                    return EntityPageSectionAssignments;
                                }
                                while (OReader.Read())
                                {
                                    EntityPageSectionAssignment EntityPageSectionAssignment = new EntityPageSectionAssignment();
                                    EntityPageSectionAssignment.PageSectionAssignmentId = (Guid)OReader["PageSectionAssignmentId"]; ;
                                    EntityPageSectionAssignment.PageId = (Int32)OReader["PageId"];
                                    EntityPageSectionAssignment.PageSectionId = (Guid)OReader["PageSectionId"];
                                    EntityPageSectionAssignment.Ordinal = (Int32)OReader["Ordinal"];
                                    EntityPageSectionAssignment.TotalRecords = (int)OReader["TotalRecords"];

                                    EntityPageSectionAssignment.Page = new EntityPage();
                                    EntityPageSectionAssignment.Page.PageId = (int)OReader["PageId"];
                                    EntityPageSectionAssignment.Page.PageName = OReader["PageName"].ToString();
                                    EntityPageSectionAssignment.Page.PageTitle = OReader["PageTitle"].ToString();
                                    EntityPageSectionAssignment.Page.CurrentStatus = (int)OReader["CurrentStatus"];
                                    EntityPageSectionAssignment.Page.PageDescription = OReader["PageDescription"].ToString();
                                    EntityPageSectionAssignment.Page.ActionName = OReader["ActionName"].ToString();
                                    EntityPageSectionAssignment.Page.ControllerName = OReader["ControllerName"].ToString();

                                    EntityPageSectionAssignment.PageSection = new EntityPageSectionPk();
                                    EntityPageSectionAssignment.PageSection.PageSectionId = (Guid)OReader["PageSectionId"];
                                    EntityPageSectionAssignment.PageSection.PageSectionName = OReader["PageSectionName"].ToString();
                                    EntityPageSectionAssignments.Add(EntityPageSectionAssignment);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return EntityPageSectionAssignments;
        }
        #endregion
    }
}
